#Oracle SQL转换

-- Oracle 
CREATE TABLE t41 (id INT PRIMARY key NOT DEFERRABLE INITIALLY IMMEDIATE 
noRELY ENABLE VALIDATE )

-- YashanDB22.2预期结果
CREATE TABLE t41 (
id INT PRIMARY KEY NOT DEFERRABLE INITIALLY IMMEDIATE NORELY ENABLE VALIDATE);

-- YashanDB23.1预期结果
-- 同YashanDB22.2

-- Oracle
CREATE TABLE teett (
id INT PRIMARY KEY USING INDEX
PCTFREE 1
INITRANS 2 LOGGING ONLINE 
TABLESPACE users REVERSE VISIBLE );

CREATE TABLE fgfg (
id INT PRIMARY KEY USING INDEX(
CREATE INDEX frs_idx ON fgfg ( id ASC ) 
PCTFREE 1
INITRANS 2 LOGGING ONLINE 
TABLESPACE users COMPRESS 1 REVERSE VISIBLE NOPARALLEL) VALIDATE
);

-- YashanDB22.2预期结果
/*The 1 SQL statement*/
CREATE TABLE teett (
 id INT PRIMARY KEY USING INDEX  
 PCTFREE 1
 INITRANS 2 LOGGING ONLINE 
 TABLESPACE users
 REVERSE
);
/*The 2 SQL statement*/
CREATE TABLE fgfg (
id INT PRIMARY KEY USING INDEX  (
CREATE INDEX frs_idx ON fgfg ( id ASC ) 
PCTFREE 1
INITRANS 2 LOGGING ONLINE 
TABLESPACE users COMPRESS 1 REVERSE  VISIBLE NOPARALLEL)
);

-- YashanDB23.1预期结果
-- 同YashanDB22.2

-- 	Oracle
CREATE TABLE contracts_sec_fs
(
contract_id NUMBER(12),
contract_name VARCHAR2(80),
file_size NUMBER,
orig_file BLOB
)
TABLESPACE users NOLOGGING;

-- YashanDB22.2预期结果
CREATE TABLE contracts_sec_fs (
contract_id NUMBER(12),
contract_name VARCHAR(80),
file_size NUMBER,
orig_file BLOB
)
NOLOGGING
TABLESPACE users;

-- YashanDB23.1预期结果
-- 同YashanDB22.2

-- 	Oracle
CREATE TABLE t1 (id INT)
STORAGE (
INITIAL 1k NEXT 1m MINEXTENTS 1 MAXEXTENTS 2 
PCTINCREASE 1 MAXSIZE 1m FREELISTS 1
FREELIST GROUPS 1 BUFFER_POOL KEEP FLASH_CACHE KEEP 
CELL_FLASH_CACHE KEEP optimal 1m encrypt)

-- YashanDB22.2预期结果
	
CREATE TABLE t1 (
id INT
)
STORAGE (
INITIAL 1k NEXT 1m
MINEXTENTS 1 MAXEXTENTS 2
PCTINCREASE 1 MAXSIZE 1m
FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL KEEP
FLASH_CACHE KEEP
CELL_FLASH_CACHE KEEP
);

-- YashanDB23.1预期结果
-- 同YashanDB22.2

-- 	Oracle
CREATE TABLE t1(id INT, provinces INT)
PARALLEL 3 CACHE read write;

-- YashanDB22.2预期结果
CREATE TABLE t1 (
id INT,
provinces INT
) READWRITE PARALLEL 3 CACHE ;

-- YashanDB23.1预期结果
-- 同YashanDB22.2

-- 	Oracle
CREATE TABLE employees1(
employee_info INT,
introduce CLOB)
LOB(introduce) STORE AS BASICFILE (TABLESPACE users);

-- YashanDB22.2预期结果
CREATE TABLE employees1 (
employee_info INT,
introduce CLOB
)
LOB (introduce) STORE AS BASICFILE( TABLESPACE users );

-- YashanDB23.1预期结果
-- 同YashanDB22.2

-- 	Oracle
/*The 1 SQL statement*/
CREATE TABLE composite_sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
) 
PARTITION BY RANGE (time_id)
SUBPARTITION BY HASH (channel_id)
(PARTITION SALES_Q1_1998 VALUES LESS THAN (TO_DATE('01-APR-1998','DD-MON-YYYY')),
PARTITION SALES_Q2_1998 VALUES LESS THAN (TO_DATE('01-JUL-1998','DD-MON-YYYY')),
PARTITION SALES_Q3_1998 VALUES LESS THAN (TO_DATE('01-OCT-1998','DD-MON-YYYY')),
PARTITION SALES_Q4_1998 VALUES LESS THAN (TO_DATE('01-JAN-1999','DD-MON-YYYY')),
PARTITION SALES_Q1_1999 VALUES LESS THAN (TO_DATE('01-APR-1999','DD-MON-YYYY')),
PARTITION SALES_Q2_1999 VALUES LESS THAN (TO_DATE('01-JUL-1999','DD-MON-YYYY')),
PARTITION SALES_Q3_1999 VALUES LESS THAN (TO_DATE('01-OCT-1999','DD-MON-YYYY')),
PARTITION SALES_Q4_1999 VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')),
PARTITION SALES_Q1_2000 VALUES LESS THAN (TO_DATE('01-APR-2000','DD-MON-YYYY')),
PARTITION SALES_Q2_2000 VALUES LESS THAN (TO_DATE('01-JUL-2000','DD-MON-YYYY'))
SUBPARTITIONS 8,
PARTITION SALES_Q3_2000 VALUES LESS THAN (TO_DATE('01-OCT-2000','DD-MON-YYYY'))
(SUBPARTITION ch_c,
SUBPARTITION ch_i,
SUBPARTITION ch_p,
SUBPARTITION ch_s,
SUBPARTITION ch_t),
PARTITION SALES_Q4_2000 VALUES LESS THAN (MAXVALUE)
SUBPARTITIONS 4);
/*The 2 SQL statement*/
CREATE TABLE customers_part (
customer_id NUMBER(6),
cust_first_name VARCHAR2(20),
cust_last_name VARCHAR2(20),
nls_territory VARCHAR2(30),
credit_limit NUMBER(9,2))
PARTITION BY RANGE (credit_limit)
SUBPARTITION BY LIST (nls_territory)
SUBPARTITION TEMPLATE
(SUBPARTITION east VALUES
('CHINA', 'JAPAN', 'INDIA', 'THAILAND'),
SUBPARTITION west VALUES
('AMERICA', 'GERMANY', 'ITALY', 'SWITZERLAND'),
SUBPARTITION other VALUES (DEFAULT))
(PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN (2500),
PARTITION p3 VALUES LESS THAN (MAXVALUE));

-- YashanDB22.2预期结果
-- YashanDB22.2无此语法

-- YashanDB23.1预期结果
/*The 1 SQL statement*/
CREATE TABLE composite_sales (
 prod_id NUMBER(6),
 cust_id NUMBER,
 time_id DATE,
 channel_id CHAR(1),
 promo_id NUMBER(6),
 quantity_sold NUMBER(3),
 amount_sold NUMBER(10,2)
)
 PARTITION BY RANGE (time_id) SUBPARTITION BY HASH (channel_id)
 (
  PARTITION SALES_Q1_1998 VALUES LESS THAN (TO_DATE('01-APR-1998', 'DD-MON-YYYY')) ,
  PARTITION SALES_Q2_1998 VALUES LESS THAN (TO_DATE('01-JUL-1998', 'DD-MON-YYYY')) ,
  PARTITION SALES_Q3_1998 VALUES LESS THAN (TO_DATE('01-OCT-1998', 'DD-MON-YYYY')) ,
  PARTITION SALES_Q4_1998 VALUES LESS THAN (TO_DATE('01-JAN-1999', 'DD-MON-YYYY')) ,
  PARTITION SALES_Q1_1999 VALUES LESS THAN (TO_DATE('01-APR-1999', 'DD-MON-YYYY')) ,
  PARTITION SALES_Q2_1999 VALUES LESS THAN (TO_DATE('01-JUL-1999', 'DD-MON-YYYY')) ,
  PARTITION SALES_Q3_1999 VALUES LESS THAN (TO_DATE('01-OCT-1999', 'DD-MON-YYYY')) ,
  PARTITION SALES_Q4_1999 VALUES LESS THAN (TO_DATE('01-JAN-2000', 'DD-MON-YYYY')) ,
  PARTITION SALES_Q1_2000 VALUES LESS THAN (TO_DATE('01-APR-2000', 'DD-MON-YYYY')) ,
  PARTITION SALES_Q2_2000 VALUES LESS THAN (TO_DATE('01-JUL-2000', 'DD-MON-YYYY')) ,
  PARTITION SALES_Q3_2000 VALUES LESS THAN (TO_DATE('01-OCT-2000', 'DD-MON-YYYY'))  (
 SUBPARTITION ch_c,
 SUBPARTITION ch_i,
 SUBPARTITION ch_p,
 SUBPARTITION ch_s,
 SUBPARTITION ch_t
 ),
  PARTITION SALES_Q4_2000 VALUES LESS THAN (MAXVALUE) );

/*The 2 SQL statement*/
CREATE TABLE customers_part (
 customer_id NUMBER(6),
 cust_first_name VARCHAR(20),
 cust_last_name VARCHAR(20),
 nls_territory VARCHAR(30),
 credit_limit NUMBER(9,2)
)
 PARTITION BY RANGE (credit_limit) SUBPARTITION BY LIST (nls_territory)
 SUBPARTITION TEMPLATE (
 SUBPARTITION east VALUES ('CHINA', 'JAPAN', 'INDIA', 'THAILAND'),
 SUBPARTITION west VALUES ('AMERICA', 'GERMANY', 'ITALY', 'SWITZERLAND'),
 SUBPARTITION other VALUES (DEFAULT)
 )
 (
  PARTITION p1 VALUES LESS THAN (1000) ,
  PARTITION p2 VALUES LESS THAN (2500) ,
  PARTITION p3 VALUES LESS THAN (MAXVALUE) );

-- 	Oracle
ALTER TABLE customers_part LOGGING read ONLY

-- YashanDB22.2预期结果
ALTER TABLE customers_part LOGGING READONLY;

-- YashanDB23.1预期结果
-- 同YashanDB22.2

-- 	Oracle
ALTER TABLE customers_part TRUNCATE PARTITION p1,p2 DROP STORAGE CASCADE;

-- YashanDB22.2预期结果
ALTER TABLE customers_part TRUNCATE PARTITION p1,p2 DROP STORAGE CASCADE;

-- YashanDB23.1预期结果
-- 同YashanDB22.2

-- 	Oracle
CREATE INDEX NEWTABLE_COLUMN1_IDX ON TEST.NEWTABLE (COLUMN1) UNUSABLE;

-- YashanDB22.2预期结果
CREATE INDEX NEWTABLE_COLUMN1_IDX ON TEST.NEWTABLE ( COLUMN1 ) UNUSABLE;

-- YashanDB23.1预期结果
-- 同YashanDB22.2

-- 	Oracle
CREATE INDEX NEWTABLE_COLUMN1_IDX ON NEWTABLE (COLUMN1 ASC,column2 DESC);

-- YashanDB22.2预期结果
CREATE INDEX NEWTABLE_COLUMN1_IDX ON NEWTABLE ( COLUMN1 ASC, column2 DESC );

-- YashanDB23.1预期结果
-- 同YashanDB22.2

-- 	Oracle
CREATE INDEX NEWTABLE_COLUMN1_IDX ON NEWTABLE (COLUMN1 ASC,column2) 
TABLESPACE users INITRANS 2 PCTFREE 1 ONLINE
COMPRESS 1 LOGGING REVERSE VISIBLE NOPARALLEL

-- YashanDB22.2预期结果
CREATE INDEX NEWTABLE_COLUMN1_IDX ON NEWTABLE ( COLUMN1 ASC, column2 )
PCTFREE 1
INITRANS 2 LOGGING ONLINE
TABLESPACE users COMPRESS 1 REVERSE VISIBLE NOPARALLEL

-- YashanDB23.1预期结果
-- 同YashanDB22.2

-- 	Oracle
CREATE OR replace FORCE NONEDITIONABLE VIEW v111 
AS SELECT 1 a FROM dual WITH READ ONLY 

-- YashanDB22.2预期结果
CREATE OR REPLACE FORCE NONEDITIONABLE VIEW v111
AS
SELECT 1 AS a
FROM dual WITH READ ONLY;

-- YashanDB23.1预期结果
-- 同YashanDB22.2

-- 	Oracle
CREATE MATERIALIZED VIEW dqh.mv_subquery TABLESPACE users
BUILD DEFERRED REFRESH COMPLETE ON demand NEXT SYSDATE + 10 / 2460
DISABLE QUERY REWRITE AS SELECT 1 FROM dual

-- YashanDB22.2预期结果
-- YashanDB22.2无此语法

-- YashanDB23.1预期结果
CREATE MATERIALIZED VIEW dqh.mv_subquery TABLESPACE users
BUILD DEFERRED REFRESH COMPLETE ON DEMAND NEXT SYSDATE + 10 / 2460
DISABLE QUERY REWRITE 
AS 
SELECT 1
FROM dual

--Oracle
CREATE USER ymp IDENTIFIED BY ymp_password PASSWORD EXPIRE
       
-- YashanDB22.2预期结果
CREATE USER ymp IDENTIFIED BY yasdb_123
-- YashanDB23.1预期结果
-- 同YashanDB22.2

--Oracle
CREATE OR replace EDITIONABLE TYPE a.b FORCE oid 'oid'  sharing =  none
DEFAULT collation using_nls_comp  
authid  definer
accessible BY (
	FUNCTION  a.b,
	PROCEDURE b.c,
	PACKAGE d.e,
	TRIGGER f.g,
	TYPE h.j
)
under a.typeb (
		a VARCHAR(200) )
	 NOT instantiable ;
-- YashanDB22.2预期结果
CREATE OR REPLACE EDITIONABLE TYPE a.b FORCE  AUTHID  CURRENT_USER 
 UNDER a.typeb (
	a VARCHAR(200)
);
-- YashanDB23.1预期结果
-- 同YashanDB22.2

--Oracle
CREATE OR replace EDITIONABLE TYPE body a.v sharing = metadata IS 
 map member FUNCTION func ( a INT  )  RETURN VARCHAR  
  pipelined AS BEGIN END ;  ,
 map member FUNCTION func ( a INT  )  RETURN VARCHAR  
  pipelined AS BEGIN END ;
end;
-- YashanDB22.2预期结果
CREATE OR REPLACE EDITIONABLE TYPE BODY a.v IS
	MAP MEMBER FUNCTION func (a INT) RETURN VARCHAR
	IS
	BEGIN
	END;
	MAP MEMBER FUNCTION func (a INT) RETURN VARCHAR
	IS
	BEGIN
	END;
END;
-- YashanDB23.1预期结果
-- 同YashanDB22.2

--Oracle
CREATE TYPE YMP_2854_TYPE_CONVERT.employee_constructor_typ AS OBJECT (
  employee_id   NUMBER,
  first_name    VARCHAR2(50),
  last_name     VARCHAR2(50),
  hire_date     DATE,
  CONSTRUCTOR FUNCTION VARCHAR (p_id NUMBER, p_first_name VARCHAR2, p_last_name VARCHAR2, p_hire_date DATE)
  RETURN SELF AS RESULT AS LANGUAGE JAVA NAME string,
  PRAGMA RESTRICT_REFERENCES (DEFAULT, RNDS,WNDS)
) NOT PERSISTABLE;
-- YashanDB22.2预期结果
CREATE  TYPE YMP_2854_TYPE_CONVERT.employee_constructor_typ
 AS OBJECT (
	employee_id NUMBER, 
	first_name VARCHAR(50), 
	last_name VARCHAR(50), 
	hire_date DATE, 
	 CONSTRUCTOR FUNCTION VARCHAR (p_id NUMBER, p_first_name VARCHAR, p_last_name VARCHAR, p_hire_date DATE) RETURN SELF AS RESULT 
);
-- YashanDB23.1预期结果
-- 同YashanDB22.2

--Oracle
CREATE OR replace EDITIONABLE TRIGGER t.t1  sharing =metadata DEFAULT collation using_nls_comp
BEFORE
DELETE OR INSERT  OR UPDATE OR UPDATE OF col,col2 ON xxx.tttt
referencing old AS old  NEW AS NEW 
FOR each row 
forward crossedition
follows  tt.t2
ENABLE
WHEN( 1=1)
BEGIN END ;
-- YashanDB22.2预期结果
CREATE OR REPLACE EDITIONABLE TRIGGER t.t1
BEFORE
 DELETE OR INSERT OR UPDATE OR UPDATE OF col,col2 ON xxx.tttt
REFERENCING  OLD AS old NEW AS NEW
FOR EACH ROW 
 FOLLOWS tt.t2
WHEN 1 = 1
BEGIN
END;
-- YashanDB23.1预期结果
-- 同YashanDB22.2

-- oracle
CREATE OR REPLACE EDITIONABLE 
FUNCTION FUNCTION_RESULT_CACHE
(in_parameter1 IN VARCHAR2,in_parameter2 IN VARCHAR2) 
RETURN VARCHAR2 RESULT_CACHE
RELIES_ON() 
AS result VARCHAR2(50);
BEGIN 
result := in_parameter1 || '_' || in_parameter2; 
RETURN result; 
END;
-- YashanDB22.2预期结果
CREATE OR REPLACE EDITIONABLE FUNCTION FUNCTION_RESULT_CACHE (
  in_parameter1 IN VARCHAR,
  in_parameter2 IN VARCHAR
)
RETURN VARCHAR
AS
result VARCHAR(50);
BEGIN
  result := in_parameter1 || '_' || in_parameter2;
  RETURN result;
END;
-- YashanDB23.1预期结果
-- 同YashanDB22.2

-- oracle
CREATE EDITIONABLE PROCEDURE PROCEDURE1 ( COL0 INT ) 
            AUTHID CURRENT_USER AS SALARY_NEW INT; APPRAISE_NEW CHAR ( 1 ); CASE_NUM CONSTANT INT := 1;
            DONE CONSTANT BOOLEAN := TRUE; BOOL BOOLEAN DEFAULT FALSE; CURSOR C ( JOB VARCHAR2 ) IS 
            SELECT SALARY, APPRAISE FROM EMPLOYEES WHERE JOB = JOB; BEGIN OPEN C ( 'DEPLOYMENT' ); 
            WHILE DONE LOOP FETCH C INTO SALARY_NEW, APPRAISE_NEW; EXIT WHEN C % NOTFOUND; 
            CASE WHEN CASE_NUM > 0 THEN BOOL := TRUE; ELSE BOOL := FALSE; END CASE; END LOOP; CLOSE C; END;
-- YashanDB22.2预期结果
CREATE PROCEDURE PROCEDURE1 (
  COL0 INT
) AUTHID  CURRENT_USER 
AS
  SALARY_NEW INT;
  APPRAISE_NEW CHAR(1);
  CASE_NUM CONSTANT INT := 1;
  DONE CONSTANT BOOLEAN := true;
  BOOL BOOLEAN := false;
  CURSOR C(JOB VARCHAR) IS
    SELECT SALARY, APPRAISE
    FROM EMPLOYEES
    WHERE JOB = JOB;
BEGIN
  OPEN C('DEPLOYMENT');
  WHILE DONE LOOP
  FETCH C INTO SALARY_NEW, APPRAISE_NEW;
  EXIT WHEN C % NOTFOUND;
  CASE
    WHEN CASE_NUM > 0 THEN BOOL := true;
    ELSE BOOL := false;
  END CASE;
  END LOOP;
  CLOSE C;
END;
-- YashanDB23.1预期结果
-- 同YashanDB22.2

-- oracle
CREATE OR REPLACE EDITIONABLE PACKAGE PACKAGE1
AUTHID CURRENT_USER 
ACCESSIBLE BY(FUNCTION FUNCTION1)
AS PACKAGE_SUMMARY INT := 0;
TYPE PACKAGE_AVG IS RECORD(COL0 INT);
END PACKAGE1;
-- YashanDB22.2预期结果
CREATE OR REPLACE EDITIONABLE PACKAGE PACKAGE1 AUTHID  CURRENT_USER  AS 
	PACKAGE_SUMMARY INT := 0;
	TYPE PACKAGE_AVG IS RECORD(
		COL0 INT
	);
END PACKAGE1;
-- YashanDB23.1预期结果
-- 同YashanDB22.2